--select top 10 * from tblPersonnelKB
--select PPersonCode, PDisplayName, PPersonLogin, PPassword, ICNumber, PAdmin from tblPersonnelKB Where Convert(varchar(8), PCreatedDate, 112) = '20150216'

--EXEC sprocCreateKBUser 'G3085046M', 'Junghyun Kim'

ALTER PROCEDURE sprocCreateKBUser
@ICNumber varchar(9),
@PDisplayName varchar(250)
AS
Declare @PPersonCode int
--Declare @PDisplayName nvarchar(100)
--Declare @ICNumber varchar(9)
Declare @PPersonLogin varchar(5)
Declare @PPassword varchar(8)
Declare @IsAdmin bit
Declare @PTeam smallint

--SET @ICNumber = 'S8539914D'
--SET @PDisplayName = 'Daryl Tan'

SET @ICNumber = RTRIM(LTRIM(@ICNumber))
SET @PDisplayName = RTRIM(LTRIM(@PDisplayName))

SET @PPersonLogin = SUBSTRING(@ICNumber, 5, 5)
SET @IsAdmin = 0

IF LEN(@ICNumber) = 9 And LEN(@PDisplayName) > 0
BEGIN
	IF NOT EXISTS(Select top 1 PPersonLogin from tblPersonnelKB Where PPersonLogin = @PPersonLogin)
	BEGIN
		Select @PPersonCode = Max(PPersonCode) from tblPersonnelKB
		SET @PPersonCode = @PPersonCode + 1

		IF @IsAdmin = 1 
			SET @PTeam = 2
		ELSE
			SET @PTeam = 1

	
		SET @PPassword = LEFT(@ICNumber, 1) + RIGHT(@ICNumber, 1) + LOWER(LEFT(@PDisplayName, 2)) + CAST(CAST(Rand() * 10 AS INT) AS varchar) + CAST(CAST(Rand() * 10 AS INT) AS varchar) + CAST(CAST(Rand() * 10 AS INT) AS varchar) + CAST(CAST(Rand() * 10 AS INT) AS varchar) 
		--Select @PPersonCode PPersonCode, @PPersonLogin PPersonLogin,  @PPassword PPassword, @IsAdmin IsAdmin

		INSERT INTO [dbo].[tblPersonnelKB]
			   ([PPersonCode]
			   ,[PPersonLogin]
			   ,[PPassword]
			   ,[PDisplayName]
			   ,[PActive]
			   ,[PCampaign]
			   ,[ICNumber]
			   ,[PPersonLevel]
			   ,[PTeam]
			   ,[PTeamLeader]
			   ,[PSinSales]
			   ,[PCreatedDate]
			   ,[PQuizLevel]
			   ,[PAdmin])
		 VALUES
			   (@PPersonCode
			   ,@PPersonLogin
			   ,@PPassword
			   ,@PDisplayName
			   ,1
			   ,'Krisflyer'
			   ,@ICNumber
			   ,4
			   ,@PTeam
			   ,@IsAdmin
			   ,0
			   ,GETDATE()
			   ,NULL
			   ,@IsAdmin)

		Select * from tblPersonnelKB Where PPersonCode = @PPersonCode
	END
	ELSE
	BEGIN
		Select 'FOUND EXISTING ID', * from tblPersonnelKB Where PPersonLogin = @PPersonLogin
	END
END
ELSE
BEGIN
	Select 'Invalid Entry'
END